SherbyNet - /page/wikibook/entry.mu

Micron Document

<!DOCTYPE html>

<title>Object-PL/SQL</title>

</head>

Object-PL/SQL

</h1>

</header>

<div id="mw-content-text" class="mw-body-content mw-content-ltr" lang="en" dir="ltr"><div class="mw-content-ltr mw-parser-output" lang="en" dir="ltr">Object-PL/SQL (<a href="../Object-oriented_programming" title="Object-oriented programming">Object</a>-Procedural Language/<a href="../Structured_Query_Language" class="mw-redirect" title="Structured Query Language">Structured Query Language</a> or simply O-PL/SQL) is a methodology of using the <a href="../Oracle_Corporation" title="Oracle Corporation">Oracle Corporation</a>'s <a href="../Procedural_programming" title="Procedural programming">procedural</a> extension <a href="../Programming_language" title="Programming language">language</a> for <a href="../SQL" title="SQL">SQL</a> and the <a href="../Oracle_Database" title="Oracle Database">Oracle relational database</a>.<a href="#cite_note-CITDK-1">[1]</a> <a href="#cite_note-2">[2]</a> The additional features from version 7 and other improvements, lead to one of the large-scale environment implementations of the <a href="../Object-oriented_database" class="mw-redirect" title="Object-oriented database">object-oriented database</a> paradigm.<a href="#cite_note-DBAORA-3">[3]</a>

Although PL/SQL's general <a href="../Syntax_(programming_languages)" title="Syntax (programming languages)">syntax</a> formerly used to resemble that of <a href="../Ada_(programming_language)" title="Ada (programming language)">Ada</a> or <a href="../Pascal_(programming_language)" title="Pascal (programming language)">Pascal</a>, there were many improvements that mainly include the Java embedding code<a href="#cite_note-STKOVER-4">[4]</a> and the object-oriented syntax<a href="#cite_note-ETUT-5">[5]</a> inside the SQL.

The mixing and embedding of <a href="../Database_trigger" title="Database trigger">triggers</a> and <a href="../Stored_procedure" title="Stored procedure">stored procedures</a> was one of the breakthrough points up to support the use of <a href="../PL/SQL" title="PL/SQL">PL/SQL</a> in a OO paradigm.<a href="#cite_note-SAMS-6">[6]</a> The inclusion in the SQL syntax of statements such as <style data-mw-deduplicate="TemplateStyles:r886049734">

/* start https://en.wikipedia.org/ */

.mw-parser-output .monospaced{font-family:monospace,monospace}

/* end https://en.wikipedia.org/ */

</style>[class].[object], and the implementation of the object type<a href="#cite_note-TOP10-7">[7]</a> (like any OO language), completed the minimum requisites to a mapping approach in an extended SQL language without use of specific mapping software.<a href="#cite_note-BALES-8">[8]</a>

<div class="mw-heading mw-heading2"><h2 id="Autonomy,_notoriety_and_importance_of_O-PL/SQL">Autonomy, notoriety and importance of O-PL/SQL</h2></div>

The O-PSL/SQL isn't simply the use a version of a programming language but it's identified as how to use it, and it defines the autonomy of the theme.<a href="#cite_note-JAVA2-9">[9]</a> Each version of PL/SQL, starting from 7, brings so many innovations that it's impossible to treat such usages as sub-themes of PL/SQL. So big is that revolution that it establishes a real borderline between the language, that can be used as formerly, and the OO approach inside itself. It's just this approach that makes the theme important and the large-scale using has brought its notoriety.<a href="#cite_note-STEVE-10">[10]</a>

<div class="mw-heading mw-heading2"><h2 id="A_confusing_of_objects">A confusing of objects</h2></div>

There can be confusion of the notions of object of DBMS and of class object. This is very important as we live with both significances in one language. It's necessary to identify when the documentation refers to an object as one of the two definitions.

Database objects are concepts that refer to <a href="../Relational_model" title="Relational model">relational</a> or sequential databases and persist being valid in new models. Tables, triggers, columns, indexes are examples of database objects,<a href="#cite_note-TOP10-7">[7]</a> which are present in O-PL/SQL, but with the same meaning of the notion of <a href="../Java_(programming_language)" title="Java (programming language)">Java</a> objects, specifically an element of a set that has its existence beginning from an instantiation of a class.

/* start https://en.wikipedia.org/ */

.mw-parser-output .hatnote{font-style:italic}.mw-parser-output div.hatnote{padding-left:1.6em;margin-bottom:0.5em}.mw-parser-output .hatnote i{font-style:normal}.mw-parser-output .hatnote+link+.hatnote{margin-top:-0.5em}@media print{body.ns-0 .mw-parser-output .hatnote{display:none!important}}

/* end https://en.wikipedia.org/ */

</style><div role="note" class="hatnote navigation-not-searchable">Main article: <a href="../PL/SQL" title="PL/SQL">PL/SQL</a></div>

PL/SQL is the extended SQL language used by <a href="../Oracle_Database" title="Oracle Database">Oracle Database</a>.

PL/SQL is available in Oracle Database (since version 7), <a href="../TimesTen" title="TimesTen">TimesTen in-memory database</a> (since version 11.2.1), and <a href="../IBM_Db2" title="IBM Db2">IBM Db2</a> (since version 9.7).<a href="#cite_note-IBM-11">[11]</a>

O-PL/SQL allows the definition of classes and instantiating these as objects,

thus creating user-defined datatypes as writing constructors, beyond using Java in stored procedures and triggers.

<div class="mw-heading mw-heading2"><h2 id="Examples_of_uses_of_syntax_of_O-PL/SQL">Examples of uses of syntax of O-PL/SQL</h2></div>

Here is a small set of examples of O-PL/SQL syntax, extracted from the official documentation<a href="#cite_note-ORADOC-12">[12]</a> and other sources:

A simple example of object-oriented PL/SQL<a href="#cite_note-13">[13]</a>

<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre>create or replace type base_type as object (

a number,

constructor function base_type return self as result,

member function func return number,

member procedure proc (n number)

) instantiable not final;

/

</pre></div>

Now, the type's implementation is created. The implementation defines how the type's functions, procedures and how explicit constructors behave:<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre>create or replace type body base_type as

begin

a:=0;

return;

end base_type;

begin

return a;

end func;

begin

a:=n;

end proc;

end;

/

</pre></div>

We're ready to derive from base_type. The keyword for deriving is under. The derived type defines a new attribute (named: m) and overrides func.<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre>create or replace type deriv_type under base_type (

m number,

overriding member function func return number

);

/

</pre></div>

As is the case with base types, the overridden methods in the derived type must be implemented:

<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre>create or replace type body deriv_type as

begin

return m*a;

end;

end;

/

</pre></div>

The created types can be instantiated and methods can be called:

<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre>declare

b1 base_type :=base_type();

b2 base_type :=base_type(4);

d1 deriv_type:=deriv_type(5,6);

d2 deriv_type:=deriv_type(5,6);

begin

dbms_output.put_line(b1.func);

dbms_output.put_line(b2.func);

d1.proc(4);

dbms_output.put_line(d1.func);

dbms_output.put_line(d2.func);

end;

/

</pre></div>

Results

<pre>0

</pre>

The created types have become real types and can be used in tables:

<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre>create table table_base (

b base_type

);

declare

base base_type := base_type();

deriv deriv_type:= deriv_type(8,9);

begin

insert into table_base values(base);

end;

/

select t.b.func() from table_base t;

</pre></div>

Results:

<pre>0

</pre>

<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre>select avg(t.b.func()) from table_base t;

</pre></div>

Result:

<pre>36

</pre>

<ul><li><a href="../Relational_database_management_system" class="mw-redirect" title="Relational database management system">Relational database management system</a></li>

<li><a href="../Component-oriented_database" title="Component-oriented database">Component-oriented database</a></li>

<li><a href="../Transact-SQL" title="Transact-SQL">Transact-SQL</a></li>

<li><a href="../Database_management_system" class="mw-redirect" title="Database management system">Database management system</a></li></ul>

<div class="mw-heading mw-heading2"><h2 id="Bibliography">Bibliography</h2></div>

/* start https://en.wikipedia.org/ */

.mw-parser-output cite.citation{font-style:inherit;word-wrap:break-word}.mw-parser-output .citation q{quotes:"\"""\"""'""'"}.mw-parser-output .citation:target{background-color:rgba(0,127,255,0.133)}.mw-parser-output .id-lock-free.id-lock-free a{background:url("../mw/Lock-green.svg")right 0.1em center/9px no-repeat}.mw-parser-output .id-lock-limited.id-lock-limited a,.mw-parser-output .id-lock-registration.id-lock-registration a{background:url("../mw/Lock-gray-alt-2.svg")right 0.1em center/9px no-repeat}.mw-parser-output .id-lock-subscription.id-lock-subscription a{background:url("../mw/Lock-red-alt-2.svg")right 0.1em center/9px no-repeat}.mw-parser-output .cs1-ws-icon a{background:url("../mw/Wikisource-logo.svg")right 0.1em center/12px no-repeat}body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-free a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-limited a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-registration a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-subscription a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .cs1-ws-icon a{background-size:contain;padding:0 1em 0 0}.mw-parser-output .cs1-code{color:inherit;background:inherit;border:none;padding:inherit}.mw-parser-output .cs1-hidden-error{display:none;color:var(--color-error,#d33)}.mw-parser-output .cs1-visible-error{color:var(--color-error,#d33)}.mw-parser-output .cs1-maint{display:none;color:#085;margin-left:0.3em}.mw-parser-output .cs1-kern-left{padding-left:0.2em}.mw-parser-output .cs1-kern-right{padding-right:0.2em}.mw-parser-output .citation .mw-selflink{font-weight:inherit}@media screen{.mw-parser-output .cs1-format{font-size:95%}html.skin-theme-clientpref-night .mw-parser-output .cs1-maint{color:#18911f}}@media screen and (prefers-color-scheme:dark){html.skin-theme-clientpref-os .mw-parser-output .cs1-maint{color:#18911f}}

/* end https://en.wikipedia.org/ */

</style><cite id="CITEREFBennett2002" class="citation book cs1">Bennett, Mathew (2002). <a rel="nofollow" class="external text" href="https://books.google.com/books?id=1RwFA-vlwX8C&q=mixing+java+in+pl%2Fsql+procedures+and+triggers&pg=PA144">Programming Oracle Developer</a> (1 ed.). Indianapolis: Sams. p. 348. <a href="../ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <bdi>0672321106</bdi>.</cite></li>

<li><cite id="CITEREFBales2007" class="citation book cs1">Bales, Donals (2007). <a rel="nofollow" class="external text" href="https://books.google.com/books?id=FqUKI3MSuHYC">PL/SQL from Novice to Professional</a> (1 ed.). New York: Apress. p. 469. <a href="../ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <bdi>978-1590598825</bdi>.</cite></li>

<li><cite id="CITEREFFeuersteinPribyl2009" class="citation book cs1">Feuerstein, Steven; Pribyl, Bill (2009). <a rel="nofollow" class="external text" href="https://web.archive.org/web/20131225213946/http://my.safaribooksonline.com/book/databases/oracle-pl-sql/9780596805401">"26"</a>. Oracle PL/SQL Programming. Sebastopol: O'Reilly Media, Inc. <a href="../ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <bdi>9780596514464</bdi>. Archived from <a rel="nofollow" class="external text" href="http://my.safaribooksonline.com/book/databases/oracle-pl-sql/9780596805401">the original</a> on 2013-12-25. Retrieved 2012-04-20.</cite></li>

<li><cite id="CITEREFRahayutaniarPardede2006" class="citation book cs1">Rahayu, Wenny; taniar, David; Pardede, Eric (2006). <a rel="nofollow" class="external text" href="https://web.archive.org/web/20160303223746/http://218.92.71.5:1085/trebook/book/book36/2009986047703.pdf">Object-Oriented Oracle</a> (PDF). Hershey: IRM Press. p. 345. <a href="../ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <bdi>1591406080</bdi>. Archived from <a rel="nofollow" class="external text" href="http://218.92.71.5:1085/trebook/book/book36/2009986047703.pdf">the original</a> (PDF) on 2016-03-03. Retrieved 2012-04-20.</cite></li></ul>

<div class="mw-heading mw-heading2"><h2 id="References">References</h2></div>

/* start https://en.wikipedia.org/ */

.mw-parser-output .reflist{margin-bottom:0.5em;list-style-type:decimal}@media screen{.mw-parser-output .reflist{font-size:90%}}.mw-parser-output .reflist .references{font-size:100%;margin-bottom:0;list-style-type:inherit}.mw-parser-output .reflist-columns-2{column-width:30em}.mw-parser-output .reflist-columns-3{column-width:25em}.mw-parser-output .reflist-columns{margin-top:0.3em}.mw-parser-output .reflist-columns ol{margin-top:0}.mw-parser-output .reflist-columns li{page-break-inside:avoid;break-inside:avoid-column}.mw-parser-output .reflist-upper-alpha{list-style-type:upper-alpha}.mw-parser-output .reflist-upper-roman{list-style-type:upper-roman}.mw-parser-output .reflist-lower-alpha{list-style-type:lower-alpha}.mw-parser-output .reflist-lower-greek{list-style-type:lower-greek}.mw-parser-output .reflist-lower-roman{list-style-type:lower-roman}

/* end https://en.wikipedia.org/ */

</style><div class="reflist">

<li id="cite_note-CITDK-1"><a href="#cite_ref-CITDK_1-0">^</a> <cite id="CITEREFLassan,_Alan_R.Due,_Jacob_Steen2000" class="citation web cs1">Lassan, Alan R.; Due, Jacob Steen (13 June 2000). <a rel="nofollow" class="external text" href="https://web.archive.org/web/20101224020624/http://www.cit.dk/cot/reports/reports/Case4/18/cot-4-18.pdf">"Experiences with Object Oriented Development in PL/SQL"</a> (PDF). The danish National Center for IT Research. Archived from <a rel="nofollow" class="external text" href="http://www.cit.dk/cot/reports/reports/Case4/18/cot-4-18.pdf">the original</a> (PDF) on 24 December 2010. Retrieved 15 April 2012.</cite>

</li>

<li id="cite_note-2"><a href="#cite_ref-2">^</a> <cite id="CITEREFCentre_ForAllan_R._LassenJacob_Steen_Due2000" class="citation journal cs1">Centre For; Allan R. Lassen; Jacob Steen Due (2000). "Experiences with Object Oriented Development in PL/SQL". <a href="../CiteSeerX_(identifier)" class="mw-redirect" title="CiteSeerX (identifier)">CiteSeerX</a> <a rel="nofollow" class="external text" href="https://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.38.5122">10.1.1.38.5122</a>.</cite> <code class="cs1-code">{{cite journal}}</code>: Cite journal requires <code class="cs1-code">|journal=</code> (help)

</li>

<li id="cite_note-DBAORA-3"><a href="#cite_ref-DBAORA_3-0">^</a> <cite id="CITEREFCunningham,_Lewis" class="citation web cs1">Cunningham, Lewis. <a rel="nofollow" class="external text" href="http://www.dba-oracle.com/t_edb_pl_sql_features_release.htm">"PL/SQL Features by Release"</a>. Burleson Consulting. Retrieved 15 April 2012.</cite>

</li>

<li id="cite_note-STKOVER-4"><a href="#cite_ref-STKOVER_4-0">^</a> <cite class="citation web cs1"><a rel="nofollow" class="external text" href="https://stackoverflow.com/questions/70072/when-should-you-use-java-stored-procedures-with-an-oracle-database-what-are">"When Should you use Java Stored Procedures with an Oracle Database, what are the Drawbacks?"</a>. Stack Overflow. Retrieved 15 April 2012.</cite>

</li>

<li id="cite_note-ETUT-5"><a href="#cite_ref-ETUT_5-0">^</a> <cite class="citation web cs1"><a rel="nofollow" class="external text" href="http://etutorials.org/SQL/Oracle+PL+SQL+Language+Pocket+Reference/Chapter+1.+Oracle+PLSQL+Language+Pocket+Reference/1.16+Oracles+Object-Oriented+Features/">"Oracle's Object-Oriented Features"</a>. etutorial.org. Retrieved 16 April 2012.</cite>

</li>

<li id="cite_note-SAMS-6"><a href="#cite_ref-SAMS_6-0">^</a> Benett, 2002:144

</li>

<li id="cite_note-TOP10-7">^ <a href="#cite_ref-TOP10_7-0">a</a> <a href="#cite_ref-TOP10_7-1">b</a> <cite id="CITEREFShubho,_Al-Farooque2009" class="citation web cs1">Shubho, Al-Farooque (8 November 2009). <a rel="nofollow" class="external text" href="http://www.codeproject.com/Articles/43629/Top-10-steps-to-optimize-data-access-in-SQL-Server">"Optimize Database Files and Apply Partitioning"</a>. The Code Project. Retrieved 19 April 2012.</cite>

</li>

<li id="cite_note-BALES-8"><a href="#cite_ref-BALES_8-0">^</a> Bales, 2007:107-209

</li>

<li id="cite_note-JAVA2-9"><a href="#cite_ref-JAVA2_9-0">^</a> <cite class="citation web cs1"><a rel="nofollow" class="external text" href="http://www.java2s.com/Tutorial/Oracle/0620__Object-Oriented/UseObjectPLSQL.htm">"Use Object PL/SQL"</a>. java2s.com. Retrieved 19 April 2012.</cite>

</li>

<li id="cite_note-STEVE-10"><a href="#cite_ref-STEVE_10-0">^</a> Feuerstein, 2009

</li>

<li id="cite_note-IBM-11"><a href="#cite_ref-IBM_11-0">^</a> <cite class="citation web cs1"><a rel="nofollow" class="external text" href="http://www.ibm.com/developerworks/data/library/techarticle/dm-0907oracleappsondb2/index.html">"DB2 10: Run Oracle applications on DB2 10 for Linux, UNIX, and Windows"</a>. IBM. Retrieved 20 April 2012.</cite>

</li>

<li id="cite_note-ORADOC-12"><a href="#cite_ref-ORADOC_12-0">^</a> <cite class="citation web cs1"><a rel="nofollow" class="external text" href="http://docs.oracle.com/">"Oracle Documentatio"</a>. Oracle. Retrieved 19 April 2012.</cite>

</li>

<li id="cite_note-13"><a href="#cite_ref-13">^</a> <cite class="citation web cs1"><a rel="nofollow" class="external text" href="http://www.adp-gmbh.ch/ora/plsql/oo/example_1.html">"Object Oriented Oracle, example 1"</a>. René Nyffenegger's collection of things on the web. Retrieved 19 April 2012.</cite>

</li>

</ol></div></div>

<div class="mw-heading mw-heading2"><h2 id="External_sources">External sources</h2></div>

<ul><li><a rel="nofollow" class="external text" href="https://web.archive.org/web/20120917012254/http://elearning.algonquincollege.com/coursemat/dat2355d/oracle-oo.html">Examples of O-Pl/SQL</a></li>

<li>Another example of stored procedure in Java embedded in Oracle Documentation: <cite class="citation web cs1"><a rel="nofollow" class="external text" href="http://docs.oracle.com/cd/B19306_01/java.102/b14187/chthree.htm#CACFGDCI">"Calling Java Methods in Oracle Database"</a>. Oracle. Retrieved 20 April 2012.</cite></li></ul></div><div><div class="zim-footer">

This article is issued from <a class="external text" title="Last edited on 2025-07-19" href="https://en.wikipedia.org/wiki/?title=Object-PL%2FSQL&oldid=1301375522">Wikipedia</a>. The text is available under <a class="external text" href="https://creativecommons.org/licenses/by-sa/4.0/deed.en">Creative Commons Attribution-Share Alike 4.0</a> unless otherwise noted. Additional terms may apply for the media files.

</div>

</div></div>

</div>

</main>

</div>

</body></html>